package com.lanqiao.guimei.dao;

import com.lanqiao.guimei.commons.JdbcUtil;
import com.lanqiao.guimei.commons.ResultSetHandler;
import com.lanqiao.guimei.vo.Page;
import com.lanqiao.guimei.vo.TradeDetail;
import com.lanqiao.guimei.vo.TradeInfo;
import javafx.beans.binding.ObjectExpression;
import oracle.jdbc.oracore.OracleType;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;

/**
 * @author 韩原
 * @date 2019/8/14  11:46
 */
public class TradeInfoDao extends BaseDao {
    Logger logger = Logger.getLogger(TradeInfoDao.class);

    /**
     * 查询总记录数
     *
     * @param page
     * @return
     */
    public int countForPage(Page<TradeInfo> page, String time1, String time2) {
        TradeInfo tradeInfo = page.getQueryObject();
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer("select count(1) cnt from " +
                "trade_info f,guimei_users u where mark=1 and f.buyer=u.user_id");
        // 生成查询条件，且将占位符对应位置的值存入列表
        //交易编号
        if (StringUtils.isNoneBlank(tradeInfo.getTrdNo())) {
            sql.append(" and f.trd_no like ?");
            params.add("%" + tradeInfo.getTrdNo() + "%");
        }
        //买家
        if (StringUtils.isNoneBlank(tradeInfo.getLoginname())) {
            sql.append(" and u.login_name like ?");
            params.add("%" + tradeInfo.getLoginname() + "%");
        }
        //交易起始时间
        if (StringUtils.isNoneBlank(time1)) {
            sql.append(" and f.trd_time > to_date( ? ,'yyyy-mm-dd')");
            params.add(time1);
        }
        //交易结束时间
        if (StringUtils.isNoneBlank(time2)) {
            sql.append(" and f.trd_time < to_date(?,'yyyy-mm-dd')");
            params.add(time2);
        }
        try (Connection conn = JdbcUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql.toString());
        ) {
            for (int i = 0; i < params.size(); i++) {
                // 设置占位符的值
                ps.setObject(i + 1, params.get(i));
            }
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                // 查询结果只有一列，指定列索引
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            logger.error("统计总记录数. ", e);
        }
        return 0;
    }

    /**
     * 分页查询
     *
     * @param page
     * @return
     */
    public List<TradeInfo> findForPage(Page<TradeInfo> page, String time1, String time2) {
        TradeInfo tradeInfo = page.getQueryObject();
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer("select * from(" +
                "   select t.*,ROWNUM rn from (" +
                "       select f.trd_no trdNo ,u.login_name loginname,f.total_money totalMoney," +
                "f.trd_time trdTime,f.trd_loc trdLoc ,f.receiver,f.phone,f.mark from " +
                " trade_info f,guimei_users u where f.buyer=u.user_id  ");
        // 生成查询条件，且将占位符对应位置的值存入列表
        //交易编号
        if (StringUtils.isNoneBlank(tradeInfo.getTrdNo())) {
            sql.append(" and f.trd_no like ?");
            params.add("%" + tradeInfo.getTrdNo() + "%");
        }
        //买家
        if (StringUtils.isNoneBlank(tradeInfo.getLoginname())) {
            sql.append(" and u.login_name like ?");
            params.add("%" + tradeInfo.getLoginname() + "%");
        }
        //交易起始时间
        if (StringUtils.isNoneBlank(time1)) {
            sql.append(" and f.trd_time > to_date( ? ,'yyyy-mm-dd')");
            params.add(time1);
        }
        //交易结束时间
        if (StringUtils.isNoneBlank(time2)) {
            sql.append(" and f.trd_time < to_date(?,'yyyy-mm-dd')");
            params.add(time2);
        }
        sql.append("and mark =1 order by f.trd_no");

        sql.append(") t where ROWNUM<=? " +
                ") where rn>=?");
        params.add(page.getLastIndex());
        System.out.println(page.getLastIndex());
        params.add(page.getFirstIndex());
        System.out.println(page.getFirstIndex());
        try {
            return runner.query(sql.toString(),
                    new BeanListHandler<TradeInfo>(TradeInfo.class),
                    params.toArray());
        } catch (SQLException e) {
            logger.error("分页查询失败。", e);
        }
        return null;
    }

    /**
     * 更新
     *
     * @param tradeInfo
     * @return
     */
    public Boolean trdupdata(TradeInfo tradeInfo) {
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer("update trade_info set");

        if (StringUtils.isNoneBlank(tradeInfo.getTrdLoc())) {
            sql.append(" trd_loc = ? ,");
            params.add(tradeInfo.getTrdLoc());
        }
        if (StringUtils.isNoneBlank(tradeInfo.getReceiver())) {
            sql.append(" receiver = ? ,");
            params.add(tradeInfo.getReceiver());
        }
        if (StringUtils.isNoneBlank(tradeInfo.getPhone())) {
            sql.append(" phone = ? ,");
            params.add(tradeInfo.getPhone());
        }
        // 去掉最后面的一个逗号
        sql.deleteCharAt(sql.lastIndexOf(","));

        sql.append("where trd_no=?");

        params.add(tradeInfo.getTrdNo());
        try {
            int inserted = runner.update(sql.toString(), params.toArray());
            return (inserted == 1 ? true : false);
        } catch (SQLException e) {
            System.err.println("更新失败：" + e.getMessage());
        }
        return false;
    }

    /**
     * 删除
     *
     * @param tradeInfo
     * @return
     */
    public Boolean trddelete(TradeInfo tradeInfo) {
        String sql = "update trade_info set mark =0 where trd_no = ? ";

        try {
            int deleted = runner.update(sql, tradeInfo.getTrdNo());
            return (deleted == 1 ? true : false);

        } catch (SQLException e) {
            System.err.println("删除失败：" + e.getMessage());
        }
        return false;
    }

    /**
     * 查询明细
     *
     * @param tradeInfo
     * @return
     */
    public List<TradeDetail> trdfind(TradeInfo tradeInfo) {
        String sql = "select d.trd_no trdNo,p.prod_name prodName,d.saler," +
                "d.quantity,d.discount_price discountPrice,p.image " +
                "from trade_detail d,product p where d.prod_no=p.prod_no and d.trd_no = ?";
        try {
            return runner.query(sql,
                    new BeanListHandler<TradeDetail>(TradeDetail.class),
                    tradeInfo.getTrdNo());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }




    /*
     * 添加订单 和明细表
     * */
    public String addTrade(TradeInfo newTradeInfo, List<TradeDetail> tradeDetails) {
        Connection conn = JdbcUtil.getConnection();
        try {
            String trdNo = runner.query(conn,"select getOrderNo() from dual", new ScalarHandler<String>());

            conn.setAutoCommit(false);
            /*
            create table trade_info(
                 trd_no varchar2(18) not null primary key,             --交易编号, yyyyMMddhhmmss+oracle4位随机数
                 buyer number references guimei_users (user_id),       --买家
                 total_money number(8,2),                              --交易总金额=product.price*quantity
                 trd_time timestamp default systimestamp,              --交易时间，系统默认
                 trd_loc varchar2(100),      --收件地址
                 receiver varchar2(20),     --收件人
                 phone varchar2(15),        --联系电话
                 mark number(2)             --标记（0代表订单删除，1代表订单存在）
                );
            */
            String trdSql = "insert into trade_info(trd_no,buyer,total_money,trd_loc,receiver,phone,mark) " +
                    "values(?,?,?,?,?,?,?)";
            int trdInfoInserted = runner.update(conn,
                    trdSql,
                    trdNo,
                    newTradeInfo.getBuyer(),
                    newTradeInfo.getTotalMoney(),
                    newTradeInfo.getTrdLoc(),
                    newTradeInfo.getReceiver(),
                    newTradeInfo.getPhone(),
                    1);
            /*
            create table trade_detail(
                 trd_no varchar2(18) references trade_info(trd_no),           --交易编号
                 prod_no varchar2(40) not null references product(prod_no),   --外键，商品编号
                 saler number references guimei_users(user_id),               --卖家
                 quantity number,                         --交易数量
                 discount_price number(8,2),              --折扣价、优惠价
                 primary key(trd_no,prod_no)              --复合主键
                );
             */
            String trdInfoDetailSql = "insert into trade_detail values(?,?,?,?,?)";
            Object[][] values = new Object[tradeDetails.size()][6];
            for (int i = 0; i < tradeDetails.size(); i++) {
                TradeDetail tradeDetail = tradeDetails.get(i);
                values[i] = new Object[]{
                        trdNo,
                        tradeDetail.getProdNo(),
                        tradeDetail.getSaler(),
                        tradeDetail.getQuantity(),
                        tradeDetail.getDiscountPrice(),
                };
            }
            int[] batchInserted = runner.batch(conn, trdInfoDetailSql, values);

            conn.commit();

//            CallableStatement callableStatement = connection.prepareCall("{call insertTrd(?,?,?,?,?,?)}");
//            callableStatement.registerOutParameter(1, Types.VARCHAR);
//            callableStatement.setInt(2, newTradeInfo.getBuyer());
//            callableStatement.setInt(3, newTradeInfo.getTotalMoney());
//            callableStatement.setString(4, newTradeInfo.getTrdLoc());
//            callableStatement.setString(5, newTradeInfo.getReceiver());
//            callableStatement.setString(6, newTradeInfo.getPhone());
//            callableStatement.execute();
//            tno = callableStatement.getString(1);
            return trdNo;
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return null;
    }

//    public static void main(String[] args) {
//        TradeInfo tradeInfo = new TradeInfo(null, 29, 5468, new Timestamp(new Date().getTime()), "深圳", "账", "1215454", null, null);
////        String s = new TradeInfoDao().addTrade(tradeInfo);
////        if (s != null) {
////            System.out.println(s);
////        }
//    }
}
